package hotel.discard;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import hotel.common.JDBC;
import hotel.modal.RoomType;

public class RoomTypeSql {
	protected static Connection conn = null;
	protected static Statement stmt = null;
	protected static ResultSet rs = null;
	
	// 获取所有房间类型信息
	public static ArrayList<RoomType> getAll() {
		String sql;
		ArrayList<RoomType> data = new ArrayList<RoomType>();
		try {
			conn = JDBC.getConnection();
			if (conn != null) {
				stmt = conn.createStatement();
				sql = "SELECT TypeID,TypeName,HourRoom,BedNum,Deposit,Price FROM room_type order by TypeID";
				//System.out.println(sql);
				rs = stmt.executeQuery(sql);
				String typeName;
				int typeId, hourRoom, bedNum;
				double deposit,price;
				while (rs.next()) {
					typeName = rs.getString("TypeName");
					typeId = rs.getInt("TypeID");
					hourRoom = rs.getInt("HourRoom");
					bedNum = rs.getInt("BedNum");
					deposit = rs.getDouble("Deposit");
					price = rs.getDouble("Price");
					
					RoomType roomType = new RoomType(typeId, typeName, hourRoom, bedNum, deposit, price);
					data.add(roomType);
				}
				stmt.close();
				
				 /*System.out.println("数据库表Student已有数据" + data.size() + "条");
				 for (int i = 0; i < data.size(); i++) {
					 System.out.println(data.get(i).toString()); 
				 }*/
				 
			}
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		} finally {
			RoomTypeSql.closeAll();
		}
		return data;
	}
	
	// 获取房间类型信息
	public static RoomType get() {
		String sql;
		RoomType roomType = null;
		try {
			conn = JDBC.getConnection();
			if (conn != null) {
				stmt = conn.createStatement();
				sql = "SELECT TypeID,TypeName,HourRoom,BedNum,Deposit,Price FROM room_type order by TypeID";
				// System.out.println(sql);
				rs = stmt.executeQuery(sql);
				String typeName;
				int typeId, hourRoom, bedNum;
				double deposit, price;
				while (rs.next()) {
					typeName = rs.getString("TypeName");
					typeId = rs.getInt("TypeID");
					hourRoom = rs.getInt("HourRoom");
					bedNum = rs.getInt("BedNum");
					deposit = rs.getDouble("Deposit");
					price = rs.getDouble("Price");
					roomType = new RoomType(typeId, typeName, hourRoom, bedNum, deposit, price);
				}
				stmt.close();
			}
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		} finally {
			RoomTypeSql.closeAll();
		}
		return roomType;
	}
	
	// 添加房间类型信息
	public static Boolean add(RoomType roomType) {
		String sql;
		int ret = 0;
		if (roomType == null)
			return false;
		try {
			conn = JDBC.getConnection();
			if (conn == null) {
				return false;
			}
			stmt = conn.createStatement();
			sql = String.format(
					"insert into room_type(TypeID,TypeName,HourRoom,BedNum,"
							+ "Deposit,Price) values(%d,'%s',%d,%d,%.2f,%.2f)",
					roomType.getTypeId(), roomType.getBedNum(), (roomType.isHourRoom() ? 1 : 0), roomType.getBedNum(),
					roomType.getDeposit(), roomType.getPrice());
			// System.out.println(sql);
			ret = stmt.executeUpdate(sql);
			stmt.close();
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		} finally {
			RoomTypeSql.closeAll();
		}
		if (ret == 1)
			return true;
		else
			return false;
	}

	// 更新房间信息
	public static Boolean update(RoomType roomType) {
		String sql;
		int ret = 0;
		try {
			conn = JDBC.getConnection();
			if (conn == null) {
				return false;
			}
			stmt = conn.createStatement();
			sql = String.format(
					"update room_type set TypeName='%s',"
							+ "HourRoom=%d,BedNum=%d,Deposit=%.2f,Price=%.2f where TypeID=%d",
					roomType.getTypeName(), (roomType.isHourRoom() ? 1 : 0), roomType.getBedNum(), roomType.getDeposit(),
					roomType.getPrice(), roomType.getTypeId());
			//System.out.println(sql);
			ret = stmt.executeUpdate(sql);
			stmt.close();
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		} finally {
			RoomTypeSql.closeAll();
		}
		if (ret == 1)
			return true;
		else
			return false;
	}

	// 删除房间信息
	public static Boolean delete(RoomType roomType) {
		String sql;
		int ret = 0;
		try {
			conn = JDBC.getConnection();
			if (conn == null) {
				return false;
			}
			stmt = conn.createStatement();
			sql = String.format("delete from room_type where TypeID=%d", roomType.getTypeId());
			//System.out.println(sql);
			ret = stmt.executeUpdate(sql);
			stmt.close();
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		} finally {
			RoomTypeSql.closeAll();
		}
		if (ret == 1)
			return true;
		else
			return false;
	}
	
	// 添加获取下一个Id
	public static int getNextId() {
		String sql;
		int id = -1;
		try {
			conn = JDBC.getConnection();
			if (conn == null) {
				return -1;
			}
			stmt = conn.createStatement();
			sql = "SELECT max(TypeID) from room_type";
			//System.out.println(sql);
			rs = stmt.executeQuery(sql);
			if(rs.next()) id = rs.getInt(1);
			stmt.close();
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		}finally {
			RoomTypeSql.closeAll();
		}
		return id + 1;
	}

	private static void closeAll() {
		if (rs != null) try { rs.close(); } catch(Exception e) {}
	    if (stmt != null) try { stmt.close(); } catch(Exception e) {}
	    if (conn != null) try { conn.close(); } catch(Exception e) {}
	}
}
